/*
 Navicat MySQL Data Transfer

 Source Server         : javaee
 Source Server Type    : MySQL
 Source Server Version : 80015
 Source Host           : localhost:3306
 Source Schema         : bookdb

 Target Server Type    : MySQL
 Target Server Version : 80015
 File Encoding         : 65001

 Date: 27/06/2019 22:28:00
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for booktype
-- ----------------------------

DROP TABLE IF EXISTS `booktype`;
CREATE TABLE `booktype` (
                            `id` int(4) NOT NULL AUTO_INCREMENT,
                            `typename` varchar(30) DEFAULT NULL,
                            PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


-- ----------------------------
-- Records of booktype
-- ----------------------------
BEGIN;
INSERT INTO `booktype` VALUES (1, '计算机');
INSERT INTO `booktype` VALUES (2, '英语');
INSERT INTO `booktype` VALUES (3, '电子科技');
INSERT INTO `booktype` VALUES (4, '建筑工程');
COMMIT;

-- ----------------------------
-- Table structure for book
-- ----------------------------
DROP TABLE IF EXISTS `book`;
CREATE TABLE `book` (
                      `bookid` int(11) NOT NULL AUTO_INCREMENT,
                      `isbn` char(10) NOT NULL,
                      `typeid` int(4) DEFAULT NULL,
                      `bookname` varchar(30) DEFAULT NULL,
                      `author` varchar(30) DEFAULT NULL,
                      `publish` varchar(30) DEFAULT NULL,
                      `publishdate` date DEFAULT NULL,
                      `publishtime` int(4) DEFAULT NULL,
                      `unitprice` float(4,2) DEFAULT NULL,
                      `status` int(2) NOT NULL DEFAULT '0',
                      PRIMARY KEY (`bookid`) USING BTREE,
                      KEY `typeid` (`typeid`),
                      CONSTRAINT `book_ibfk_1` FOREIGN KEY (`typeid`) REFERENCES `booktype` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of book
-- ----------------------------
BEGIN;
INSERT INTO `book` VALUES (1, '25698745', 1, 'AI', '吴恩达', '人民出版社', '2016-05-23', 5, 36.00, 0);
INSERT INTO `book` VALUES (3, '2658545', 2, '计算机英语', '唐晓敏', '北京邮电出版社', '2017-05-06', 6, 36.00, 0);
INSERT INTO `book` VALUES (5, '9876321', 1, 'javaEE', '唐晓丹', '人民出版社', '2019-06-25', 5, 49.00, 0);
INSERT INTO `book` VALUES (7, '98723658', 1, '计算机组成原理', '宋丹', '西安电子科技出版社', '2017-05-06', 4, 39.00, 0);
INSERT INTO `book` VALUES (8, '98785846', 3, '机器人系统', '宋敏慧', '电子工业出版社', '2018-02-03', 2, 34.00, 0);
INSERT INTO `book` VALUES (9, '98546987', 4, '建筑原理与应用', '李敏镐', '电子工业出版社', '2019-01-02', 1, 59.00, 0);
INSERT INTO `book` VALUES (10, '98564256', 2, '大学英语一', '吴晓航', '北京英语出版社', '2017-08-09', 3, 48.00, 1);
INSERT INTO `book` VALUES (11, '98565854', 3, '数字模拟电路', '但永平', '郑州中原出版社', '2016-05-09', 5, 56.00, 0);
INSERT INTO `book` VALUES (12, '85898758', 4, '楼房构造原理', '赵胡敏', '大连工业出版社', '2016-09-08', 6, 69.00, 0);
INSERT INTO `book` VALUES (13, '85965845', 2, '大学英语三', '吴晓航', '北京英语出版社', '2016-05-09', 4, 59.00, 0);
INSERT INTO `book` VALUES (14, '25698541', 2, '大学英语二', '吴晓航', '北京英语出版社', '2017-04-06', 3, 59.00, 0);
INSERT INTO `book` VALUES (15, '25698541', 2, '大学英语四', '吴晓航', '北京英语出版社', '2019-05-06', 1, 59.00, 0);
INSERT INTO `book` VALUES (16, '25698745', 1, 'AI', '吴恩达', '人民出版社', '2016-05-23', 5, 36.00, 0);
INSERT INTO `book` VALUES (17, '25698745', 1, 'AI', '吴恩达', '人民出版社', '2016-05-23', 5, 36.00, 1);
INSERT INTO `book` VALUES (18, '25698745', 1, 'AI', '吴恩达', '人民出版社', '2016-05-23', 5, 36.00, 0);
INSERT INTO `book` VALUES (19, '98546987', 4, '建筑原理与应用', '李敏镐', '电子工业出版社', '2019-01-02', 1, 59.00, 0);
INSERT INTO `book` VALUES (20, '98546987', 4, '建筑原理与应用', '李敏镐', '电子工业出版社', '2019-01-02', 1, 59.00, 1);
INSERT INTO `book` VALUES (21, '98546987', 4, '建筑原理与应用', '李敏镐', '电子工业出版社', '2019-01-02', 1, 59.00, 0);
INSERT INTO `book` VALUES (22, '98546987', 4, '建筑原理与应用', '李敏镐', '电子工业出版社', '2019-01-02', 1, 59.00, 0);
COMMIT;


-- ----------------------------
-- Table structure for readertype
-- ----------------------------
DROP TABLE IF EXISTS `readertype`;
CREATE TABLE `readertype` (
                              `id` int(4) NOT NULL AUTO_INCREMENT,
                              `typename` varchar(20) DEFAULT NULL,
                              `maxborrownum` int(2) DEFAULT NULL,
                              `limitday` int(3) DEFAULT NULL,
                              PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of readertype
-- ----------------------------
BEGIN;
INSERT INTO `readertype` VALUES (1, '教师', 10, 90);
INSERT INTO `readertype` VALUES (2, '学生', 5, 60);
COMMIT;

-- ----------------------------
-- Table structure for users
-- ----------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
                         `id` int(11) DEFAULT NULL,
                         `name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
                         `passwd` varchar(20) DEFAULT NULL,
                         `role` int(2) NOT NULL DEFAULT '0',
                         PRIMARY KEY (`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of users
-- ----------------------------
BEGIN;
INSERT INTO `users` VALUES (5, 'jmx', '123456', 1);
INSERT INTO `users` VALUES (35, 'lf', '123456', 1);
INSERT INTO `users` VALUES (3, 'lrl', '123456', 1);
INSERT INTO `users` VALUES (10, 'user', '123456', 0);
INSERT INTO `users` VALUES (0, 'user1', '123456', 0);
INSERT INTO `users` VALUES (36, 'xww', '123456', 1);
INSERT INTO `users` VALUES (4, 'yf', '654321', 1);
INSERT INTO `users` VALUES (9, 'yrl', '123456', 1);
COMMIT;


-- ----------------------------
-- Table structure for reader
-- ----------------------------
DROP TABLE IF EXISTS `reader`;
CREATE TABLE `reader` (
                          `readerid` varchar(12) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
                          `type` int(4) DEFAULT NULL,
                          `name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
                          `age` int(3) DEFAULT NULL,
                          `sex` char(4) DEFAULT NULL,
                          `phone` char(11) DEFAULT NULL,
                          `dept` varchar(20) DEFAULT NULL,
                          `regdate` date DEFAULT NULL,
                          PRIMARY KEY (`readerid`) USING BTREE,
                          KEY `type` (`type`),
                          KEY `name` (`name`),
                          CONSTRAINT `reader_ibfk_1` FOREIGN KEY (`type`) REFERENCES `readertype` (`id`) ON DELETE CASCADE,
                          CONSTRAINT `reader_ibfk_2` FOREIGN KEY (`name`) REFERENCES `users` (`name`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of reader
-- ----------------------------
BEGIN;
INSERT INTO `reader` VALUES ('201608030203', 1, 'xww', 20, '女', '13526584597', 'CS', '2019-05-06');
INSERT INTO `reader` VALUES ('201608030204', 1, 'jmx', 26, '女', '13635987569', '网络工程', '2019-03-05');
INSERT INTO `reader` VALUES ('201608030319', 2, 'user', 22, '男', '12565985847', '网络工程', '2016-05-09');
INSERT INTO `reader` VALUES ('201608030320', 1, 'lf', 21, '男', '13526548978', 'CS', '2019-08-03');
INSERT INTO `reader` VALUES ('201608030321', 1, 'yrl', 22, '男', '13526548371', 'CS', '2019-07-03');
INSERT INTO `reader` VALUES ('201608030324', 1, 'yf', 22, '男', '13526548371', 'CS', '2019-07-03');
INSERT INTO `reader` VALUES ('201608030325', 1, 'lrl', 22, '男', '13526548371', 'CS', '2019-07-03');
INSERT INTO `reader` VALUES ('201608030328', 1, 'user1', 21, '女', '12356487985', 'CS', '2019-06-27');
COMMIT;

-- ----------------------------
-- Table structure for borrowbook
-- ----------------------------
DROP TABLE IF EXISTS `borrowbook`;
CREATE TABLE `borrowbook` (
                            `readerid` varchar(12) NOT NULL,
                            `bookid` int(11) NOT NULL,
                            `borrowdate` date DEFAULT NULL,
                            `shouldreturn` date DEFAULT NULL,
                            `returndate` date DEFAULT NULL,
                            `fine` float(4,2) DEFAULT NULL,
                            `flag` int(2) NOT NULL DEFAULT '0',
                            PRIMARY KEY (`readerid`,`bookid`) USING BTREE,
                            KEY `bookid` (`bookid`),
                            CONSTRAINT `borrowbook_ibfk_1` FOREIGN KEY (`readerid`) REFERENCES `reader` (`readerid`) ON DELETE CASCADE,
                            CONSTRAINT `borrowbook_ibfk_2` FOREIGN KEY (`bookid`) REFERENCES `book` (`bookid`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of borrowbook
-- ----------------------------
BEGIN;
INSERT INTO `borrowbook` VALUES ('201608030320', 5, '2019-06-27', '2019-09-25', '2019-06-27', 0.00, 1);
INSERT INTO `borrowbook` VALUES ('201608030321', 1, '2019-02-05', '2019-05-05', '2019-04-27', 0.00, 1);
INSERT INTO `borrowbook` VALUES ('201608030321', 9, '2019-06-27', '2019-09-25', '2019-06-27', 0.00, 1);
INSERT INTO `borrowbook` VALUES ('201608030321', 15, '2019-06-27', '2019-09-25', '2019-06-27', 0.00, 1);
INSERT INTO `borrowbook` VALUES ('201608030321', 16, '2019-06-03', '2019-06-20', NULL, NULL, 0);
INSERT INTO `borrowbook` VALUES ('201608030324', 8, '2019-06-03', '2019-09-03', NULL, NULL, 0);
INSERT INTO `borrowbook` VALUES ('201608030325', 7, '2019-06-03', '2019-06-20', NULL, NULL, 0);
COMMIT;


SET FOREIGN_KEY_CHECKS = 1;
